package edu.jit.flowers.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import edu.jit.flowers.bean.Orders;
import edu.jit.flowers.utils.JDBCUtil;

public class OrdersDAO {
	//添加订单
    public int addOrders(Orders o) {
   	 int rows=0;
   	 String sql="insert into orders values(0,?,?,?,?,?,?)";
   	 
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql, o.getoUName(),o.getoTotal(),new Date(0).toString(),o.getoComment(),o.getoStatus());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   	 return rows;
    }
    //删除订单
    public int deleteOrdersById(Orders o) {
   	 int rows=0;
   	 String sql="delete from orders where o_id = ?";
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql, new BeanHandler<>(Orders.class),o.getoId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
   	 return rows;
    }
    //修改订单总数
    public int updateOrdersTotalById(Orders o) {
   	 int rows=0;
   	 String sql="update orders set o_total=? where o_id=?";
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql, new BeanHandler<>(Orders.class),o.getoTotal(),o.getoId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
   	 return rows;
   	 
    }
  //修改订单是否发送的状态
    public int updateOrdersStatusById(Orders o) {
   	 int rows=0;
   	 String sql="update orders set o_status=? where o_id=?";
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql, new BeanHandler<>(Orders.class),o.getoStatus(),o.getoId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
   	 return rows;
   	 
    }
    //查看所有订单
    public List<Orders> selectAll(){
		List<Orders> orders = new ArrayList<>();
		String sql="select o_id as oid,o_u_id as ouid,o_u_name as ouname,o_total as ototal, "
				+ "o_date as odate,o_comment as ocomment,o_status as ostatus "
				+ "from orders";
		QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
		try {
			orders=runner.query(sql, new BeanListHandler<>(Orders.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       return orders;
    }
    //根据id查询所有订单
    public List<Orders> selectAllById(){
		List<Orders> orders = new ArrayList<>();
		String sql="select o_id as oid,o_u_id as ouid,o_u_name as ouname,o_total as ototal,"
				+ "o_date as odate,o_comment as ocomment,o_status as ostatus "
				+ "from orders,users where orders.ouid=users.u_id";
		QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
		try {
			orders=runner.query(sql, new BeanListHandler<>(Orders.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       return orders;
    }
  //根据用户名查询所有订单
    public List<Orders> selectAllByUName(String username){
		List<Orders> orders = new ArrayList<>();
		String sql="select o_id as oid,o_u_id as ouid,o_u_name as ouname,o_total as ototal,"
				+ "o_date as odate,o_comment as ocomment,o_status as ostatus "
				+ "from orders,users where orders.o_u_name=?";
		QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
		try {
			orders=runner.query(sql, new BeanListHandler<>(Orders.class),username);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       return orders;
    }

}
